5  Company Data

Summary of useful data for Grolsch beer production and distribution:

Background Information

You can find plenty of information about Grolsch beer production and distribution in the Case Study References section. Read the reports and articles to get a better understanding of the company’s operations and fill in the data gaps in the provided datasets using plausible assumptions and online data.

5.1 General

Parameter Value Description
Warehouse Pallet Capacity | 19,500 | The maximum number of pallets that can be stored in the warehouse. This limit is the same for all facilities. |
Warehouse Average Pallets | 18,415 | The average number of pallets in the warehouse. |

5.2 Product Data

The product data contains information about the products sold by Grolsch. Download the data here.

  • product_id: The unique identifier of the product.
  • product_name: The name of the product.
  • price_eur: The price of the product in Euros.
  • volume_unit_liter: The volume of a single unit of the product in liters.
  • unit_count_per_pack: The number of units in the product pack (e.g., 24-pack, 6-pack, keg).
  • category: The category of the product.
    • Non-alcoholic: Beer with 0.0% alcohol content.
    • Radler: A mix of beer and lemonade.
    • Beer: Regular beer with alcohol content.
  • alcohol_content: The alcohol content of the product.
Table 5.1: Grolsch product portfolio data.
product_name price_eur volume_unit_liter unit_count_per_pack category alcohol_content
product_id
P001 Grolsch 0.0 24-pack 22.64 0.30 24 Non-Alcoholic 0.0%
P003 Grolsch 0.0% 6 pack 4.79 0.30 6 Non-Alcoholic 0.0%
P007 Grolsch Blond 12 bruine beugels a 45cl 29.95 0.45 12 Beer 6.5%
P008 Grolsch Herfstbok 19.5Ltr 162.50 19.50 1 Beer 6.5%
P006 Grolsch India Pale Lager met Speciaalbier Glas... 47.64 0.33 6 Beer 6.5%
P009 Grolsch Pilsner 0.0% 24x30cl Alcoholvrij 19.49 0.30 24 Non-Alcoholic 0.0%
P011 Grolsch Pilsner 24x30cl 20.99 0.30 24 Beer 5.0%
P014 Grolsch Pilsner Beugels 16x45cl 20.99 0.45 16 Beer 5.0%
P015 Grolsch Pilsner Blik 24x33cl 25.99 0.33 24 Beer 5.0%
P017 Grolsch Pilsner Fust 19.5Ltr 97.49 19.50 1 Beer 5.0%

5.3 Product Pallet Requirements

Different products have different pallet requirements based on their volume and packaging. You can find the product pallet data here.

Columns:

  • product_id: The unique identifier of the product.
  • quantity_per_pallet: The number of units of the product that can fit on a pallet.
  • total_volume_pallet: The total volume of the product that can fit on a pallet in liters.
Table 5.2: Grolsch product pallet requirements data. A pallet can hold a certain number of units of each product based on the packaging.
quantity_per_pallet total_volume_pallet
product_id
P001 120 862
P003 480 862
P007 120 648
P008 24 468
P006 240 474

5.4 Facilities Data

The facilities data contains information about the locations of De Klok Dranken—Grolsch’s distribution centers (DCs)—and the Grolsch brewery.

Download the data here.

Data columns:

  • facility_id: The unique identifier of the facility.
  • name: The name of the facility.
  • category: The category of the facility.
  • address: The address of the facility.
  • opening_hours: The opening hours of the facility.
  • phone: The phone number of the facility.
  • latitude: The latitude of the facility.
  • longitude: The longitude of the facility.
Table 5.3: Grolsch brewery and De Klok Dranken locations data. The main company warehouse is located in Enschede, Netherlands, next to the factory.
name category address opening_hours phone latitude longitude
facility_id
F6 De Klok Dranken DC Fort Blauwkapel 6, 1358 DB Almere Mon-Fri: 08:00 - 17:00 085 047 1900 52.404467 5.323641
F5 De Klok Dranken DC Groothandelsweg 5, 2645 EH Delfgauw Mon-Fri: 08:00 - 17:00, Fri: 08:00 - 15:00 085 047 1900 52.169252 4.323886
F4 De Klok Dranken DC Grasbeemd 12, 5705 DG Helmond Mon-Fri: 08:00 - 17:00 085 047 1900 51.551838 5.620272
F3 De Klok Dranken DC Bolwerk 5, 7141 JM Groenlo Mon-Fri: 08:00 - 17:00 085 047 1900 52.169252 6.642001
F2 De Klok Dranken DC Brouwerslaan 1, 7548 XA Enschede Mon-Fri: 08:00 - 17:00 085 047 1900 52.350813 6.894686
Make this Notebook Trusted to load map: File -> Trust Notebook
Figure 5.1: Map of De Klok Dranken locations and the Grolsch brewery.

5.5 Customers Data

The customers data contains the Dutch supermarkets that are customers of De Klok Dranken.

Download the data here.

Data columns:

  • customer_id: The unique identifier of the customer.
  • name: The name of the customer.
  • store_count: The number of stores owned by the customer.
  • group: The group to which the customer belongs.
  • year_founded: The year the customer was founded.
  • summary: A summary of the customer’s history.
Table 5.4: Grolsch customers data.
name store_count group year_founded summary
customer_id
C001 Albert Heijn 1048 Ahold Delhaize 27 May 1887 Albert Heijn founded a grocery store in 1887 a...
C002 Aldi 500 Aldi Nord group 10 July 1946 Karl and Theo Albrecht's mother opened a small...
C003 Boni 43 independent 1972 Boni is a Dutch supermarket chain that was fou...
C004 Boon's Markt 15 Boon Food Group 2013 Started as MC Discount in 1966, rebranded as B...
C005 Dagwinkel 74 Van Tol B.V. 30 June 2004 Established in 2004 by Van Tol B.V.

Customer Distribution Centers

The distribution centers (DCs) are the locations where the products are stored before being delivered to the customers. Grolsch delivers products to the DCs, which then distribute the products to the stores. You can find the customer distribution centers data here.

Data columns:

  • facility_id: The unique identifier of the facility.
  • name: The name of the facility.
  • lat: The latitude of the facility.
  • lon: The longitude of the facility.
  • postcode: The postal code of the facility.
  • city: The city of the facility.
  • street: The street address of the facility.
  • housenumber: The house number of the facility.
  • opening_hours: The opening hours of the facility.
  • customer_id: The unique identifier of the customer.
Table 5.5: Customer distribution centers data.
facility_id name latitude longitude postcode city street housenumber opening_hours customer_id
0 2740816935 PLUS 52.021645 4.709531 2805CA Gouda Thorbeckelaan 1 Mo-Sa 07:15-21:00; Su 09:30-18:00 C016
1 5649942689 Lidl 52.460993 4.816992 1508HA Zaandam Boere Jonkerplein 1 Mo-Sa 07:30-20:00; Su 10:00-20:00 C011
2 1805609531 Albert Heijn 52.009689 4.360788 2611CL Delft Brabantse Turfmarkt 33 Mo-Sa 08:00-22:00; Su 11:00-22:00 C001
3 34045595 Jumbo 51.694717 5.266967 5224AR 's-Hertogenbosch Helftheuvelpassage 346 Mo-Sa 08:00-20:00; Th,Fr 08:00-21:00; Su 12:00... C010
4 34044420 Albert Heijn 51.912715 4.458232 3021GS Rotterdam Nieuwe Binnenweg 288B Mo-Sa 08:00-21:00; Su 12:00-18:00 C001
Make this Notebook Trusted to load map: File -> Trust Notebook
Figure 5.2: Map of customer distribution centers.

5.6 Travel Distance Data

The travel distance data contains the distances between the facilities (DCs and the brewery) and the customer distribution centers. The data is formatted as a distance matrix, where each row represents the distance from a facility to all other facilities. For example, the first row contains the distances from the first facility F6 to all other facilities (e.g., F6, F5, F4, F3, F2).

Download the data here.

Data columns:

  • from_facility_id: The unique identifier of the origin facility (customer or brewery DC).
  • Other columns: The unique identifiers of the destination facilities (customer or brewery DC).
Table 5.6: Travel distances between facilities (DCs and the brewery) and customer distribution centers (in kilometers).
F6 F5 F4 F3 F2 F1 F0 2740816935 5649942689 1805609531 ... 2940993132 6123357378 4756869640 1723436005 1444392966 2804896485 472763108 34046094 2750744853 1271318324
facility_id
F6 0.000000 72857.918868 96960.315577 93408.268312 106806.398707 134255.833488 103840.198503 59687.726050 34918.451790 78939.937963 ... 90319.980258 93379.479782 113003.396788 96526.350908 128826.267433 58921.342139 61079.545588 81611.181874 90898.024497 50975.774648
F5 72857.918868 0.000000 112419.182288 158087.311188 176120.411726 200211.309441 169936.366393 31038.950211 46646.294489 17920.945742 ... 159633.847020 37332.336589 72325.513713 85822.297436 154026.701935 33765.380780 31275.040024 14018.099737 21722.039384 94309.215812
F4 96960.315577 112419.182288 0.000000 98162.517097 124579.930964 209748.826148 109677.779877 81567.179569 115076.808292 100492.199215 ... 113302.794129 94895.555952 78130.968904 37988.391660 41914.811484 78899.401746 81191.164256 107458.249607 111903.579798 50008.020042
F3 93408.268312 158087.311188 98162.517097 0.000000 26520.688496 130923.338434 12552.760477 133024.130139 128223.376272 156856.166531 ... 19941.845278 163228.974695 164078.207514 127962.684950 96094.032372 130779.864474 133815.718283 162035.624532 170336.183771 68599.545050
F2 106806.398707 176120.411726 124579.930964 26520.688496 0.000000 112708.518780 16961.188108 153393.421190 141469.092025 176872.494111 ... 16549.897168 185007.322570 188494.066408 153432.023887 121453.907249 151351.123564 154326.628438 181531.420509 190214.253804 91762.250595

5 rows Ă— 57 columns

5.7 Weather Data

You can find weather data for the Netherlands online at the KNMI Data Platform. Alternatively, you can use the processed data available here.

Columns:

  • date: The date of the weather data.
  • max_temp: The maximum temperature in degrees Celsius.
  • min_temp: The minimum temperature in degrees Celsius.
  • mean_temp: The mean temperature in degrees Celsius.
  • precipitation: The total precipitation in millimeters.
Table 5.7: Weather data for the Netherlands in 2023.
max_temp min_temp mean_temp precipitation
date
2023-01-01 6.7 4.9 9.0 5.9
2023-01-02 6.2 4.4 8.3 3.8
2023-01-03 8.4 6.0 11.2 12.2
2023-01-04 8.3 6.3 11.2 1.6
2023-01-05 7.7 4.7 9.4 1.4

5.8 Order Data

The order data contains information about the orders placed by customers. A single order can contain multiple products, each with a specific quantity. These products are stored in the order line items data. You can find the order data here.

Columns:

  • order_id: The unique identifier of the order.
  • customer_id: The unique identifier of the customer.
  • order_issue_date: The date the order was issued.
  • delivery_deadline: The deadline for delivery.
  • priority_level: The priority level of the order.
  • order_status: The status of the order.
  • service_level: The service level of the order.
  • product_type: The type of product ordered.
Table 5.8: Grolsch order data. Each order can contain multiple products with specific quantities.
customer_id order_issue_date delivery_deadline
order_id
G0000000001 C011 2023-02-17 2023-02-21
G0000000002 C001 2023-08-22 2023-08-29
G0000000003 C002 2023-07-15 2023-07-19
G0000000004 C001 2023-12-06 2023-12-12
G0000000005 C015 2023-05-23 2023-05-28

5.9 Order Line Items Data

The order line items data contains information about the products ordered in each order. You can find the order line items data here.

Columns:

  • order_id: The unique identifier of the order.
  • product_id: The unique identifier of the product.
  • quantity_ordered: The quantity of the product ordered (divisible by number of units per pallet).
Table 5.9: Grolsch order line items data. Each order can contain multiple products with specific quantities.
quantity_ordered total_pallets
order_id product_id
G0000000001 P014 1440 15
P018 264 22
P008 600 25
P014 3072 32
P014 288 3

5.10 Raw Material Data

The raw material data contains information about the ingredients used in Grolsch products. You can find the data here.

The ingredients refer to the total amount used. For example, if we have a 24-pack, the ingredients are for the entire pack.

The ingredient list is as follows:

  • water: The main ingredient in beer.
  • malted barley: The primary source of fermentable sugars in beer.
  • hops: Used for flavoring and aroma in beer.
  • yeast: Used for fermentation.
  • malted wheat: Used in wheat beers.
  • lemon juice: Used in Radler beers.
  • spices: Used in special beers.
Table 5.10: Grolsch ingredients data. The raw materials used in Grolsch products (beer ingredients and packaging).
water_liters malted_barley_kgs hops_kgs yeast_kgs malted_wheat_kgs lemon_juice_liters spices_kgs
product_id
P001 7.20 1.800 0.0360 0.0180 0.000 0.00 0.00
P002 7.20 1.800 0.0360 0.0180 0.000 1.08 0.00
P003 1.80 0.450 0.0090 0.0045 0.000 0.00 0.00
P004 1.80 0.450 0.0090 0.0045 0.450 0.00 0.00
P006 1.98 0.495 0.0120 0.0060 0.000 0.00 0.00
P007 5.40 1.350 0.0330 0.0165 0.000 0.00 0.00
P008 19.50 4.875 0.1200 0.0600 0.000 0.00 0.15
P009 7.20 1.800 0.0360 0.0180 0.000 0.00 0.00
P011 7.20 1.800 0.0360 0.0180 0.000 0.00 0.00
P014 7.20 1.800 0.0360 0.0180 0.000 0.00 0.00
P015 7.92 1.980 0.0396 0.0198 0.000 0.00 0.00
P017 19.50 4.875 0.1200 0.0600 0.000 0.00 0.00
P018 50.00 12.500 0.3000 0.1500 0.000 0.00 0.00
P019 7.20 1.800 0.0360 0.0180 0.000 1.08 0.00
P022 7.20 1.800 0.0360 0.0180 0.000 1.08 0.00
P023 7.20 1.800 0.0360 0.0180 0.450 0.00 0.00
P024 19.50 4.875 0.1200 0.0600 4.875 0.00 0.00
P025 7.20 1.800 0.0360 0.0180 1.800 0.00 0.00

5.11 Packaging Data

The packaging data contains information about the packaging used for Grolsch products. You can find the data here. The number of packaging items used is based on the whole product pack. For example, if we have a 24-pack, the packaging data is for the entire pack.

The packaging data includes the following columns:

  • product_id: The unique identifier of the product.
  • plastic_crate: The number of plastic crates used.
  • can: The number of cans used.
  • cardboard_box: The number of cardboard boxes used.
  • standard_bottle: The number of standard bottles used.
  • swingtop_bottle: The number of swingtop bottles used.
  • keg_19: The number of 19.5-liter kegs used.
  • keg_50: The number of 50-liter kegs used.
  • standard_cap: The number of standard caps used.
  • label: The number of labels used.
  • swingtop_cap: The number of swingtop caps used.
  • swingtop_label: The number of swingtop labels used.
Table 5.11: Grolsch packaging data including bottles, cans, kegs, caps, and labels.
plastic_crate can cardboard_box standard_bottle swingtop_bottle keg_19 keg_50 standard_cap label swingtop_cap swingtop_label
product_id
P001 1 0 0 24 0 0 0 24 24 0 0
P003 0 0 1 6 0 0 0 6 6 0 0
P007 0 0 1 0 12 0 0 0 0 12 12
P008 0 0 0 0 0 1 0 0 0 0 0
P006 0 0 1 6 0 0 0 6 6 0 0
P009 1 0 0 24 0 0 0 24 24 0 0
P011 1 0 0 24 0 0 0 24 24 0 0
P014 0 0 1 0 16 0 0 0 0 16 16
P015 0 24 0 0 0 0 0 0 0 0 0
P017 0 0 0 0 0 1 0 0 0 0 0
P018 0 0 0 0 0 0 1 0 0 0 0
P019 1 0 0 24 0 0 0 24 24 0 0
P022 1 0 0 24 0 0 0 24 24 0 0
P002 1 0 0 24 0 0 0 24 24 0 0
P023 1 0 0 24 0 0 0 24 24 0 0
P024 0 0 0 0 0 1 0 0 0 0 0
P025 1 0 0 24 0 0 0 24 24 0 0
P004 0 0 1 6 0 0 0 6 6 0 0

5.12 Suppliers

The suppliers data contains information about the suppliers of Grolsch. You can find the data here.

  • supplier_id: The unique identifier of the supplier.
  • material: The material supplied by the supplier.
  • cost_per_unit_euro: The cost per unit in Euros.
  • lead_time_days: The lead time in days for the supplier to deliver the material.
Lead Time

The lead time is the time taken by the supplier to deliver the material after the order is placed. For example, if the lead time is 3 days, the supplier will deliver the material 3 days after the order is placed.

Table 5.12: Grolsch suppliers data. The suppliers of raw materials for Grolsch products (beer ingredients and packaging).
material cost_per_unit_euro lead_time_days
supplier_id
S001 water_liters 0.05 0
S002 malted_barley_kgs 0.40 5
S003 hops_kgs 0.70 7
S004 yeast_kgs 0.80 6
S005 malted_wheat_kgs 0.35 5
S006 lemon_juice_liters 0.60 4
S007 spices_kgs 0.50 6
S008 plastic_crate 1.20 3
S009 can 0.30 4
S010 cardboard_box 0.25 3
S011 standard_bottle 0.50 5
S012 swingtop_bottle 0.70 6
S013 keg_19 30.00 8
S014 keg_50 60.00 8
S015 standard_cap 0.02 2
S016 label 0.03 2
S017 swingtop_cap 0.05 2
S018 swingtop_label 0.03 2

5.13 Production Time

The production time data contains information about the time taken to produce Grolsch products. You can find the data here.

The columns in the production time data are as follows:

  • product_id: The unique identifier of the product.
  • fermentation_time_days: Number of days required for primary fermentation.
  • conditioning_time_days: Additional time for conditioning, lagering, or flavor development.
  • packaging_time_days: Time taken to package the beer into its final form.
  • total_production_time_days: Sum of fermentation, conditioning, and packaging times.
  • best_before_period_months: Shelf life of the product from the date of packaging.
Table 5.13: Grolsch production time data. The time taken to produce Grolsch products.
fermentation_time_days conditioning_time_days packaging_time_days total_production_time_days best_before_period_months
product_id
P001 7 0 1 8 6
P002 7 0 1 8 6
P003 7 0 1 8 6
P004 7 14 1 22 9
P006 7 19 1 27 9
P007 7 7 1 15 9
P008 7 21 1 29 9
P009 7 0 1 8 6
P011 7 21 1 29 9
P014 7 21 1 29 9
P015 7 21 1 29 9
P017 7 21 1 29 9
P018 7 21 1 29 9
P019 7 0 1 8 6
P022 7 7 1 15 9
P023 7 0 1 8 6
P024 7 14 1 22 9
P025 7 14 1 22 9
P026 7 14 1 22 9